﻿using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Main.DBHelp
{
    /// <summary>
    /// 通过固定模式代码解决sql文链接与执行问题
    /// </summary>
   public  class SelectWithJoin
    {
        /// <summary>
        /// 固定化sql语句,一般用前都要初始化,
        /// </summary>
        private static string sql = "";
        private static string selectStr = "";
        private static string filedsStr = "";
        private static string tableStr = "";
        private static string whereStr = "";
        private static string conditionStr = "";
        private static string joinStr = "";
        private static string pageSstr = "";
        private static string orderStr = "";
        private static string allSql = "";
        private static bool Isfetchsql = false;
        private static bool IsOld = true;

        private static Dictionary<object, object> condition = null;

        public SelectWithJoin()
        {
            orderStr=pageSstr = joinStr = conditionStr = whereStr =tableStr = filedsStr = selectStr = allSql = sql = "";
            condition = new Dictionary<object, object>();
            Isfetchsql = false;
            IsOld = false;
        }

        public void fetchsql(bool fetchsql)
        {
            Isfetchsql = fetchsql;
        }

        public void filed(string fileds)
        {
            filedsStr += fileds;
        }

        public void table(string tableName)
        {
            tableStr += tableName;
        }

        public void where(Dictionary<object, object> where)
        {
            foreach (var item in where)
            {
                conditionStr +=" "+ item.Key + "=?" + item.Key + "s and ";
                condition["?" + item.Key + "s"] = item.Value;
                whereStr += " " + item.Key + "=" + item.Value + " and ";
            }

            if(where.Count!=0)
            {
                conditionStr = conditionStr.Remove(conditionStr.Length - 4);
                whereStr = whereStr.Remove(whereStr.Length - 4);
            }
           
        }

        public void join(string Table, string where, string method)
        {
            joinStr +=" "+ method + " join " + Table + " on " + where;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <returns></returns>
        public void PageLimit(int page, int pageSize)
        {
            int firstSize = (page - 1) * pageSize + 1;
            int endSize = page * pageSize;
            pageSstr += " between " + firstSize + " and " + endSize;

        }

        /// <summary>
        /// 排序,默认asc
        /// </summary>
        /// <param name="filed">要排序的字段</param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public void OrderBy(string filed, string sort = " asc ")
        {
            orderStr+= " OrderBy " + filed + sort;
        }

        public sqlResult Splicingsql(bool Iscount=false)
        {
            sqlResult sr = new sqlResult();
            if (IsOld)
            {
                throw new Exception("请初始化后再使用SelectWithJoin的方法");
            }

            if (tableStr == "")
            {
                throw new Exception("表名为空");
            }

            if(Iscount)
            {
                sql += " select count(*) ";
                allSql += " select count(*) ";
            }
            else
            {
                sql += " select " + filedsStr;
                allSql += " select " + filedsStr;
            }

            sql += " from " + tableStr;
            allSql +=  " from " + tableStr;

            if (joinStr != "")
            {
                sql += joinStr;
                allSql += joinStr;
            }
            if (conditionStr != "")
            {
                sql += conditionStr;
                allSql += whereStr;
            }

            sr.sql= sql += pageSstr + orderStr;
            sr.allSql=allSql += pageSstr + orderStr;
            return sr;
        }

        public object Count()
        {
            sqlResult sr = Splicingsql(true);
            IsOld = true;
            if (Isfetchsql)
            {
                return false;
            }
            else
            {
                DataTable dt = DbHelper.GetList(sr.sql, condition);
                return PHPCanshu.DataTableToJsonWithJsonNet(dt);
            }
        }

        public string select()
        {
            sqlResult sr= Splicingsql();

            IsOld = true;
            if (Isfetchsql)
            {
                return sr.allSql;
            }
            else
            {
                DataTable dt= DbHelper.GetList(sr.sql, condition);
                return PHPCanshu.DataTableToJsonWithJsonNet(dt);
            }
        }
    }

    public class sqlResult
    {
        public string sql { set; get; }
        public string allSql { set; get; }
    }
}
